package com.aizhixin.lab.statistics.service;

import com.aizhixin.lab.common.core.DataValidity;
import com.aizhixin.lab.common.core.LabReportCore;
import com.aizhixin.lab.common.utils.PageJdbcUtil;
import com.aizhixin.lab.company.domain.LogDomain;
import com.aizhixin.lab.course.classes.dto.StuReportDTO;
import com.aizhixin.lab.course.course.entity.Course;
import com.aizhixin.lab.course.course.entity.CourseChapter;
import com.aizhixin.lab.course.course.repository.CourseChapterRepository;
import com.aizhixin.lab.course.course.service.CourseService;
import com.aizhixin.lab.statistics.domain.*;
import com.aizhixin.lab.statistics.util.ReportUtils;
import com.aizhixin.lab.task.domain.StuTaskDomain;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Slf4j
@Service
@Transactional
public class StudentService {
    @Autowired
    private PageJdbcUtil pageJdbcUtil;
    @Autowired
    private CourseChapterRepository chapterRepository;
    @Autowired
    private CourseService courseService;
    RowMapper<StudentVitalityDomain> sv = new RowMapper<StudentVitalityDomain>() {

        @Override
        public StudentVitalityDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            StudentVitalityDomain domain = new StudentVitalityDomain();
            domain.setLoginNum(rs.getInt("login_num"));
            domain.setOnlineTime(rs.getInt("online_time"));
            domain.setMaxTime(rs.getInt("max_time"));
            return domain;
        }
    };

    /**
     * 学生活跃度统计
     *
     * @param day
     * @param id
     * @return
     */
    public StudentVitalityDomain vitalityData(Integer day, Long id) {
        String sql = "SELECT SUM(t.OPERATION_TYPE='登录') login_num,SUM(TIMESTAMPDIFF(MINUTE,t.CREATED_DATE,t.LAST_MODIFIED_DATE)) online_time ,MAX(TIMESTAMPDIFF(MINUTE,t.CREATED_DATE,t.LAST_MODIFIED_DATE)) max_time FROM t_log t WHERE t.DELETE_FLAG=0 AND DATE_SUB(NOW(),INTERVAL " + day + " DAY)<t.CREATED_DATE AND t.CREATED_BY=" + id;
        List<StudentVitalityDomain> info = pageJdbcUtil.getInfo(sql, sv);
        StudentVitalityDomain studentVitalityDomain = info.get(0);
        if (studentVitalityDomain.getLoginNum() > 0) {
            studentVitalityDomain.setAvgTime(studentVitalityDomain.getOnlineTime() / studentVitalityDomain.getLoginNum());
        }
        sql = "SELECT a.CREATED_DATE FROM(SELECT MAX(TIMESTAMPDIFF(MINUTE,t.CREATED_DATE,t.LAST_MODIFIED_DATE)), t.CREATED_DATE FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + id + " AND DATE_SUB(NOW(),INTERVAL " + day + " DAY)<t.CREATED_DATE) a";
        Date date = pageJdbcUtil.getDate(sql);
        studentVitalityDomain.setMaxDataTime(date);
        return studentVitalityDomain;
    }

    RowMapper<LoginNumDomain> ln = new RowMapper<LoginNumDomain>() {

        @Override
        public LoginNumDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            LoginNumDomain domain = new LoginNumDomain();
            domain.setTime1(rs.getInt("time1"));
            domain.setTime2(rs.getInt("time2"));
            domain.setTime3(rs.getInt("time3"));
            domain.setTime4(rs.getInt("time4"));
            domain.setTime5(rs.getInt("time5"));
            domain.setTime6(rs.getInt("time6"));
            domain.setTime7(rs.getInt("time7"));

            return domain;
        }
    };

    public LoginNumDomain getLongNum(Integer day, Long id) {
        String sql = "SELECT sum(HOUR(t.CREATED_DATE) BETWEEN 0 AND 7) time1,sum(HOUR(t.CREATED_DATE) BETWEEN 7 AND 12) time2,sum(HOUR(t.CREATED_DATE) BETWEEN 12 AND 14) time3,sum(HOUR(t.CREATED_DATE) BETWEEN 14 AND 18) time4,sum(HOUR(t.CREATED_DATE) BETWEEN 18 AND 20) time5,sum(HOUR(t.CREATED_DATE) BETWEEN 20 AND 22) time6,sum(HOUR(t.CREATED_DATE) BETWEEN 22 AND 24) time7 FROM t_log t WHERE t.DELETE_FLAG=0 AND DATE_SUB(NOW(),INTERVAL " + day + " DAY)<t.CREATED_DATE AND t.CREATED_BY =" + id;
        List<LoginNumDomain> info = pageJdbcUtil.getInfo(sql, ln);
        return info.get(0);
    }

    RowMapper<ExperimentalStatisticsDomain> es = new RowMapper<ExperimentalStatisticsDomain>() {

        @Override
        public ExperimentalStatisticsDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ExperimentalStatisticsDomain domain = new ExperimentalStatisticsDomain();
            domain.setId(rs.getString("course_id"));
            domain.setCourseName(rs.getString("course_name"));
            Long stuId = rs.getLong("stu_id");
            domain.setReportNum(reportCommitNum(domain.getId(), stuId));
            domain.setTotalProgress(courseProgress(domain.getId(), stuId));
            domain.setReportEvaluate(avgReport(domain.getId(), stuId));
            domain.setTaskNum(getTaskCommitNum(domain.getId(), stuId));
            domain.setTaskEvaluate(avgTaskScore(domain.getId(), stuId));
            return domain;
        }
    };

    /**
     * 学生课程统计
     *
     * @param stuId
     * @return
     */
    public List<ExperimentalStatisticsDomain> getCourseList(Long stuId) {

        String sql = "SELECT cc.id course_id,cc.`name` course_name,tcs.stu_id stu_id FROM c_course cc LEFT JOIN t_course_class_stu tcs ON tcs.course_id=cc.id  WHERE cc.delete_flag=0 AND tcs.delete_flag=0 AND cc.type=20 AND cc.is_archive=0 AND cc.is_publish=1 AND tcs.stu_id=" + stuId;

        return pageJdbcUtil.getInfo(sql, es);
    }

    /**
     * 获取提交的报告数
     *
     * @param courseId
     * @param id
     */
    public Integer reportCommitNum(String courseId, Long id) {
        String sql = "SELECT COUNT(*) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.REVIEW_RESULT NOT in('init','uncommit') AND t.COURSE_ID='" + courseId + "' AND t.STU_ID=" + id;
        return pageJdbcUtil.getCountInt(sql);
    }

    /**
     * 获取课程进度
     *
     * @param courseId
     * @return
     */
    public Integer courseProgress(String courseId, Long stuId) {
        String sql = "SELECT(SELECT COUNT(*) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.STU_ID=" + stuId + " AND t.REVIEW_RESULT !='init' AND t.COURSE_ID='" + courseId + "')+(SELECT COUNT(*) FROM t_task_stu_infor tts LEFT JOIN t_course_class tcc ON tcc.id=tts.course_class_id WHERE tcc.course_id='" + courseId + "' AND tts.stu_id=" + stuId + " and tts.result in('pending','approved')) a";
        Integer countInt = pageJdbcUtil.getCountInt(sql);
        sql = "SELECT(SELECT COUNT(*) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.STU_ID=" + stuId + " AND t.COURSE_ID='" + courseId + "')+(SELECT COUNT(*) FROM t_task_stu_infor tts LEFT JOIN t_course_class tcc ON tcc.id=tts.course_class_id WHERE tcc.course_id='" + courseId + "' AND tts.stu_id=" + stuId + ") b";
        Integer countInt1 = pageJdbcUtil.getCountInt(sql);
        if (countInt1 > 0) {
            return countInt * 100 / countInt1;
        } else {
            return 0;
        }
    }

    /**
     * 获取课程学习进度
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public Integer getCourseStudyProgress(String courseId, Long stuId) {
        String sql = "SELECT COUNT(*) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.STU_ID=" + stuId + " AND t.REVIEW_RESULT !='init' AND t.COURSE_ID='" + courseId + "'";
        Integer studyNum = pageJdbcUtil.getCountInt(sql);
        sql = "SELECT COUNT(*) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.STU_ID=" + stuId + " AND t.COURSE_ID='" + courseId + "'";
        Integer totalNum = pageJdbcUtil.getCountInt(sql);

        return studyNum * 100 / totalNum;
    }

    RowMapper<String> re = new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub

            return rs.getString("result");
        }
    };

    /**
     * 课程总体评价
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public Integer avgReport(String courseId, Long stuId) {
        String sql = "SELECT t.REVIEW_RESULT result FROM t_lab_report t WHERE t.REVIEW_RESULT NOT in('init','uncommit','unchecked') and t.DELETE_FLAG=0 AND t.STU_ID=" + stuId + " AND t.COURSE_ID='" + courseId + "'";
        List<String> info = pageJdbcUtil.getInfo(sql, re);
        if (info.size() > 0) {
            Integer sum = 0;
            for (String s : info) {
                sum += ReportUtils.getScore(s);
            }

            return sum / info.size();
        } else {
            return 0;
        }
    }

    /**
     * 获取任务平均成绩
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public Integer avgTaskScore(String courseId, Long stuId) {
        String sql = "SELECT t.result FROM t_task_stu_infor t LEFT JOIN t_release_task tr ON tr.ID=t.release_task_id LEFT JOIN c_course cc ON cc.id=tr.COURSE_ID WHERE t.delete_flag=0 AND t.task_status='approved' AND cc.id='" + courseId + "' AND t.stu_id=" + stuId;
        List<String> info = pageJdbcUtil.getInfo(sql, re);
        if (info.size() > 0) {
            Integer sum = 0;
            for (String s : info) {
                sum += ReportUtils.getScore(s);
            }

            return sum / info.size();
        } else {
            return 0;
        }
    }

    /**
     * 获取任务提交数
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public Integer getTaskCommitNum(String courseId, Long stuId) {
        String sql = "SELECT count(*) FROM t_task_stu_infor t LEFT JOIN t_release_task tr ON tr.ID=t.release_task_id LEFT JOIN c_course cc ON cc.id=tr.COURSE_ID WHERE t.delete_flag=0 AND t.task_status in ('approved','pending') AND cc.id='" + courseId + "' AND t.stu_id=" + stuId;
        return pageJdbcUtil.getCountInt(sql);
    }

    /**
     * 获取报告提交数量
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public Integer getReportCommitNum(String courseId, Long stuId) {
        String sql = "SELECT COUNT(*) FROM t_lab_report t WHERE t.commit_time IS not Null AND t.delete_flag=0 AND t.COURSE_ID='" + courseId + "' AND t.STU_ID=" + stuId;
        return pageJdbcUtil.getCountInt(sql);
    }

    public Date getMaxStudyDate(Long stuId) {
        String sql = "SELECT MAX(t.LAST_MODIFIED_DATE) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.STU_ID=" + stuId;
        return pageJdbcUtil.getDate(sql);
    }

    /**
     * 获取课程总体学习情况
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public CourseStudyCaseDomain courseStudyCase(String courseId, Long stuId) {
        CourseStudyCaseDomain domain = new CourseStudyCaseDomain();
        Course course = courseService.findById(courseId);
        domain.setCourseImage(course.getImage());
        domain.setId(courseId);
        domain.setStudyTime(getMaxStudyDate(stuId));
        domain.setCourseName(course.getName());
        domain.setAvgReportScore(avgReport(courseId, stuId));
        domain.setAvgTaskScore(avgTaskScore(courseId, stuId));
        domain.setCommitReportNum(getReportCommitNum(courseId, stuId));
        domain.setCommitTaskNum(getTaskCommitNum(courseId, stuId));
        domain.setStudyCase(getCourseStudyProgress(courseId, stuId));
        return domain;
    }

    RowMapper<ChapterReportDomain> cr = new RowMapper<ChapterReportDomain>() {

        @Override
        public ChapterReportDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ChapterReportDomain domain = new ChapterReportDomain();
            domain.setChapterName(rs.getString("chapter_name"));
            domain.setReportScore(ReportUtils.getScore(rs.getString("result")));
            return domain;
        }
    };

    /**
     * 获取报告成绩
     *
     * @param chapterId
     * @param stuId
     * @return
     */
    public ChapterReportDomain getChapterReportScore(Long chapterId, Long stuId) {
        String sql = "SELECT tlr.REVIEW_RESULT result FROM t_lab_report tlr WHERE tlr.DELETE_FLAG=0 AND tlr.STU_ID=" + stuId + " AND tlr.CHAPER_ID in(SELECT cc.id FROM c_course_chapter cc WHERE cc.id=" + chapterId + " or cc.parent_id=" + chapterId + ")";
        List<String> results = pageJdbcUtil.getInfo(sql, rs);
        ChapterReportDomain domain = new ChapterReportDomain();
        int sum = 0;
        if (results.size() > 0) {
            for (String s : results) {
                Integer score = ReportUtils.getScore(s);
                if (domain.getReportMaxScore() == null || domain.getReportMaxScore() < score) {
                    domain.setReportMaxScore(score);
                }
                if (domain.getReportMinScore() == null || domain.getReportMinScore() > score) {
                    domain.setReportMinScore(score);
                }
                sum += score;
            }
            domain.setReportScore(sum / results.size());
        }
        return domain;
    }

    /**
     * 实验报告统计
     *
     * @param courseId
     * @param stuId
     * @return
     */ RowMapper<ChapterReportDomain> chapterReport = new RowMapper<ChapterReportDomain>() {

        @Override
        public ChapterReportDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ChapterReportDomain domain = new ChapterReportDomain();
            domain.setChapterName(rs.getString("chapter_name"));
            domain.setReportScore(ReportUtils.getScore(rs.getString("result")));
            return domain;
        }
    };
    public List<ChapterReportDomain> getReportScore(String courseId, Long stuId) {
//        List<CourseChapter> courseChapterList = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//        List<ChapterReportDomain> domains = new ArrayList<>();
//        for (CourseChapter c : courseChapterList) {
//            ChapterReportDomain domain = getChapterReportScore(c.getId(), stuId);
//            domain.setChapterName(c.getName());
//            domains.add(domain);
//        }
//        return domains;
        String sql="SELECT ccc.`name` chapter_name,t.REVIEW_RESULT result FROM t_lab_report t LEFT JOIN c_course_chapter ccc ON t.CHAPER_ID=ccc.id WHERE t.DELETE_FLAG=0 AND t.COURSE_ID='"+courseId+"' AND t.STU_ID="+stuId+" ORDER BY t.CREATED_DATE";
        return pageJdbcUtil.getInfo(sql,chapterReport);

    }

    /**
     * 任务成绩统计
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public List<ChapterReportDomain> getTaskAvgScore(String courseId, Long stuId) {
//        List<CourseChapter> courseChapterList = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//        List<ChapterReportDomain> domains = new ArrayList<>();
//        for (CourseChapter c : courseChapterList) {
//            ChapterReportDomain domain = getChapterTaskScore(c.getId(), stuId);
//            domain.setChapterName(c.getName());
//            domains.add(domain);
//        }
//        return domains;
        String sql="SELECT trt.TASK_NAME chapter_name,ttsi.result result FROM t_task_stu_infor ttsi LEFT JOIN t_release_task trt ON ttsi.release_task_id=trt.ID WHERE ttsi.delete_flag=0 AND trt.COURSE_ID='"+courseId+"' AND ttsi.stu_id="+stuId+" ORDER BY ttsi.CREATED_DATE";
        return pageJdbcUtil.getInfo(sql,chapterReport);
    }


    RowMapper<String> rs = new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub

            return rs.getString("result");
        }
    };

    public ChapterReportDomain getChapterTaskScore(Long chapterId, Long stuId) {
        String sql = "SELECT tt.result result FROM t_task_stu_infor tt WHERE tt.delete_flag=0 AND tt.COURSE_CHAPTER_ID=" + chapterId + " AND tt.task_status='approved' AND tt.stu_id=" + stuId;
        List<String> results = pageJdbcUtil.getInfo(sql, rs);
        ChapterReportDomain domain = new ChapterReportDomain();
        int sum = 0;
        if (results.size() > 0) {
            for (String s : results) {
                Integer score = ReportUtils.getScore(s);
                if (domain.getReportMaxScore() == null || domain.getReportMaxScore() < score) {
                    domain.setReportMaxScore(score);
                }
                if (domain.getReportMinScore() == null || domain.getReportMinScore() > score) {
                    domain.setReportMinScore(score);
                }
                sum += score;
            }
            domain.setReportScore(sum / results.size());
        }
        return domain;
    }

    RowMapper<StuTaskDomain> taskStu = new RowMapper<StuTaskDomain>() {

        @Override
        public StuTaskDomain mapRow(ResultSet rs, int rowNum) throws SQLException {
            // TODO Auto-generated method stub
            StuTaskDomain domain = new StuTaskDomain();
            domain.setId(rs.getString("id"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setTaskName(rs.getString("TASK_NAME"));
            domain.setCreateDate(rs.getDate("created_date"));
            domain.setCourseChapterName(rs.getString("course_chapter_name"));
            domain.setCourseMinutiaName(rs.getString("course_minutia_name"));
            domain.setQuestionNum(rs.getInt("question_num"));
            domain.setTaskStatus(rs.getString("task_status"));
            domain.setTaskResult(rs.getString("result"));
            return domain;
        }
    };

    /**
     * 学生任务统计页
     *
     * @param courseId
     * @param stuId
     */
    public List<StuTaskDomain> getStuTaskStatistics(String courseId, Long stuId) {
        String querySql = "SELECT tts.id , trt.TASK_NAME task_name , cc.`name` course_name, ccc1.`name` course_chapter_name , ccc2.`name` course_minutia_name,trt.CREATED_DATE created_date, tts.task_status task_status,trt.QUESTIONS_NUM question_num,tts.result result FROM t_task_stu_infor tts LEFT JOIN t_release_task trt ON tts.release_task_id = trt.ID LEFT JOIN c_course cc ON trt.COURSE_ID =cc.id LEFT JOIN c_course_chapter ccc1 ON trt.COURSE_CHAPTER_ID = ccc1.id LEFT JOIN c_course_chapter ccc2 ON trt.COURSE_MINUTIA_ID =ccc2.id WHERE tts.delete_flag = 0 ";
        querySql += " and trt.COURSE_ID='" + courseId + "'";
        querySql += " and tts.task_status='approved'";
        querySql += " and tts.stu_id=" + stuId;
        querySql += " ORDER BY tts.created_date";
        return pageJdbcUtil.getInfo(querySql, taskStu);
    }

    RowMapper<StuReportDTO> stuRm = new RowMapper<StuReportDTO>() {

        @Override
        public StuReportDTO mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            StuReportDTO domain = new StuReportDTO();
            domain.setId(rs.getString("report_id"));
            domain.setContent(rs.getString("CONTENT"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setClassName(rs.getString("class_name"));
            domain.setChapterName(rs.getString("chapter_name"));
            domain.setReviewResult(rs.getString("REVIEW_RESULT"));
            domain.setCommitTime(rs.getTimestamp("COMMIT_TIME"));
            domain.setStuName(rs.getString("stu_name"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setStuId(rs.getLong("STU_ID"));
            return domain;
        }
    };

    /**
     * 学生报告统计页
     *
     * @param courseId
     * @param stuId
     * @return
     */
    public List<StuReportDTO> getStuReportStatistics(String courseId, Long stuId) {
        String querySql = "SELECT lr.STU_ID,lr.id as report_id,lr.CONTENT,lr.REVIEW_RESULT,lr.COMMIT_TIME,u.`name` as stu_name,cl.`NAME` as class_name,cc.`name` as chapter_name,c.`name` as course_name FROM `t_lab_report` lr LEFT JOIN `c_course` c ON lr.COURSE_ID = c.id LEFT JOIN `t_user` u ON lr.stu_id = u.id LEFT JOIN `t_classes` cl ON cl.id = lr.class_id LEFT JOIN `c_course_chapter` cc ON lr.CHAPER_ID = cc.id where lr.DELETE_FLAG =0";
        querySql += " and lr.STU_ID=" + stuId + "";
        querySql += " and lr.COURSE_ID='" + courseId + "'";
        querySql += " and lr.REVIEW_RESULT not in('" + LabReportCore.REPORT_STATUS_INIT + "','" + LabReportCore.REPORT_STATUS_UNCHECKED + "','" + LabReportCore.REPORT_STATUS_UNCOMMIT + "')";
        querySql += " ORDER BY lr.CREATED_DATE";
        return pageJdbcUtil.getInfo(querySql, stuRm);
    }
}

